Option Explicit

' 用来标识表物理名的标题内容
' 用来在生成 sheet 中的所有 table 的 sql 时，定位一个 table 的开始位置
Public tablePhyNameTitleContent As String

' 是否需要在正式的 SQL 前面附加 truncate 语句
Public needTruncate As Boolean

' 在生成的 insert sql 中，是否需要列的物理名
Public needColumnPhyName As Boolean

' ------------------------------------------------
' 表物理名所在行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
Public tablePhyNameRowNo As Integer

' table 的列物理名行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
Public columnPhyNameRowNo As Integer

' table 的列类型行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
Public columnTypeRowNo As Integer

' table 的第一个数据行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
Public dataStartRowNo As Integer

' 在 sheet 中，每个 table 的起始标题列的列号(从 1 开始，这里是 table 起始位置开始计算的行号)
' 不是第一列 table 的数据所在的列，而是声明标题每一行的内容类型的那一列
'
' 扫描数据时，将会从这个列的下一列开始扫描
Public defineTitleColNo As Integer

' ------------------------------------------------

' ======================================================================
' 构造器
' ======================================================================
Private Sub Class_Initialize()
    needTruncate = True
    needColumnPhyName = True

    ' 用来标识表名的标题内容
    tablePhyNameTitleContent = "tableName"
    ' 表物理名所在行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
    tablePhyNameRowNo = 1
    ' table 的列物理名行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
    columnPhyNameRowNo = 3
    ' table 的列类型行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
    columnTypeRowNo = 4
    ' table 的第一个数据行的行号(从 1 开始，这里是 table 起始位置开始计算的行号)
    dataStartRowNo = 7
    ' 在 sheet 中，每个 table 的起始标题列的列号
    defineTitleColNo = 3
End Sub

' ======================================================================
' 生成 truncate sql
' ======================================================================
' 生成 truncate sql
' 如果没设置参数 dataRangeStr，则将会按照各项配置扫描 `sh` 中的所有表格，并为每个表格分别生成 truncate sql
' 如果设置了参数 dataRangeStr，并且参数是一个单元格，则将会以这个单元格为表格的起始位置开始向下扫描数据，并生成 truncate sql
' 如果设置了参数 dataRangeStr，并且参数是一个大的区域，则将会以这个区域的起始位置为表格的起始位置，扫描整个区域的数据，并生成 truncate sql
'
' @param sh 保存了表格数据的 Worksheet
' @optional-param dataRangeStr 被选中的区域
Public Function createTruncateSQL(ByRef sh As Worksheet, Optional dataRangeStr As String = "") As String
    ' ==================================================
    ' 1. 初始化边界
    ' ==================================================
    Dim scanDefineTitleColNo As Integer
    Dim scanStartRowNo As Long
    Dim scanEndRowNo As Long

    If dataRangeStr = "" Then
        scanDefineTitleColNo = defineTitleColNo
        scanStartRowNo = UtilsWorksheet.getColFirstUsedRowNo(sh, defineTitleColNo)
        scanEndRowNo = UtilsWorksheet.getUsedRangeEndRowNo(sh)
    Else
        ' 获取数据区域
        Dim dataRange As Range
        Set dataRange = UtilsWorksheet.getRangeByStr(sh, dataRangeStr)

        If dataRange.Count = 1 Then
            ' 如果只有一个单元格，则:
            ' 这个单元格的前一列作为: table 的定义标题列
            ' 这个单元格作为: 起始行
            ' sh 被使用的最后一行作为: 结束行
            scanDefineTitleColNo = dataRange.Column - 1
            scanStartRowNo = dataRange.Row
            scanEndRowNo = UtilsWorksheet.getUsedRangeEndRowNo(sh)
        Else
            ' 如果是一个范围，则:
            ' 整个区域的前一列作为: table 的定义标题列
            ' 整个区域的起始行作为: 起始行
            ' 整个区域的最后一行作为: 结束行
            scanDefineTitleColNo = dataRange.Column - 1
            scanStartRowNo = dataRange.Row
            scanEndRowNo = UtilsWorksheet.getRangeLastRowNo(dataRange)
        End If
    End If

    Dim tableStartColNo As Integer
    tableStartColNo = scanDefineTitleColNo + 1

    ' ==================================================
    ' 2. 扫描 sheet 中的所有 table，并生成 truncate sql
    ' ==================================================
    Dim tablePhyName As String

    ' 遍历所有的 table
    Dim ri As Long
    ri = scanStartRowNo
    While ri <= scanEndRowNo
        ' 1. 搜索下一个表名
        ri = findNextTableStartRowNo(sh, ri, scanEndRowNo, scanDefineTitleColNo)
        If ri = -1 Then
            GoTo loopAllTableBreak
        End If

        ' 2. 生成 truncate sql
        tablePhyName = sh.Cells(ri, tableStartColNo)

        createTruncateSQL = createTruncateSQL & "truncate " & tablePhyName & ";" & vbCrLf

        ' 3. 索引向后移动
        ri = ri + 1
    Wend
loopAllTableBreak:

End Function

' ======================================================================
' 生成 insert sql
' ======================================================================
' 生成 insert sql
' 如果没设置参数 dataRangeStr，则将会按照各项配置扫描 `sh` 中的所有表格，并为每个表格分别生成 insert sql
' 如果设置了参数 dataRangeStr，并且参数是一个单元格，则将会以这个单元格为表格的起始位置开始向下扫描数据，并生成 insert sql
' 如果设置了参数 dataRangeStr，并且参数是一个大的区域，则将会以这个区域的起始位置为表格的起始位置，扫描整个区域的数据，并生成 insert sql
'
' @param sh 保存了表格数据的 Worksheet
' @optional-param dataRangeStr 被选中的区域
Public Function createInsertSQL(ByRef sh As Worksheet, Optional dataRangeStr As String = "") As String

    ' ==================================================
    ' 1. 检查: 如果没设置参数 dataRangeStr，则扫描 `sh` 中的所有表格，并为每个表格分别生成 insert sql
    ' ==================================================
    If dataRangeStr = "" Then
        createInsertSQL = doCreateInsertSQLForAllTableInSheetRange(sh)
        Exit Function
    End If

    ' ==================================================
    ' 2. 获取数据区域
    ' ==================================================
    Dim dataRange As Range
    Set dataRange = UtilsWorksheet.getRangeByStr(sh, dataRangeStr)

    ' ==================================================
    ' 3. 检查: dataRangeStr 是一个单元格，则将会以这个单元格为表格的起始位置开始向下扫描数据，并生成 insert sql
    ' ==================================================
    If dataRange.Count = 1 Then
        createInsertSQL = doCreateInsertSQLFromOneCell(sh, dataRange)
    Else
    ' ==================================================
    ' 4. 否则, dataRangeStr 是一个大的区域，则将会以这个区域的起始位置为表格的起始位置，扫描整个区域的数据，并生成 insert sql
    ' ==================================================
        createInsertSQL = doCreateInsertSQLFromRange(sh, dataRange)
    End If

End Function

' ======================================================================
' 生成整个 sheet 的 insert sql
' ======================================================================
' 扫描 `sh` 指定区域中的所有表格，并为每个 table 分别生成 insert sql
' @optional-param startRowNo 扫描的起始行号。如果没有指定，则在 table 的定义标题列中，获取第一个被使用的单元格的行号
' @optional-param endRowNo 扫描的结束行号。如果没有指定，则获取: sheet 被使用范围的最后一行的行号
' @optional-param rangeDefineTitleColNo 定义标题列的列号。如果没有指定，则使用属性 [defineTitleColNo]
' @optional-param endColNo 扫描的结束列号
' @return String insert sql 字符串
Private Function doCreateInsertSQLForAllTableInSheetRange(ByRef sh As Worksheet, _
    Optional ByRef startRowNo As Long = -1, Optional ByRef endRowNo As Long = -1, _
    Optional ByRef rangeDefineTitleColNo As Integer = -1, Optional ByRef endColNo As Integer = -1) As String
    ' ==================================================
    ' 1. 初始化
    ' ==================================================
    ' 1. 如果没有在参数中设置定义标题列的列号，则使用属性 [defineTitleColNo]
    Dim scanDefineTitleColNo As Integer
    If rangeDefineTitleColNo = -1 Then
        scanDefineTitleColNo = defineTitleColNo
    Else
        scanDefineTitleColNo = rangeDefineTitleColNo
    End If

    ' 2. 确认起始行号，如果参数没有传递，则: 在 table 的定义标题列中，获取第一个被使用的单元格的行号
    Dim scanStartRowNo As Long
    If startRowNo = -1 Then
        scanStartRowNo = UtilsWorksheet.getColFirstUsedRowNo(sh, defineTitleColNo)
    Else
        scanStartRowNo = startRowNo
    End If

    ' 3. 确认结束行号，如果参数没有传递，则获取: sheet 被使用范围的最后一行的行号
    Dim scanEndRowNo As Long
    If endRowNo = -1 Then
        scanEndRowNo = UtilsWorksheet.getUsedRangeEndRowNo(sh)
    Else
        scanEndRowNo = endRowNo
    End If

    ' ==================================================
    ' 2. 扫描 sheet 中的所有 table，并生成 insert sql
    ' ==================================================
    Dim tableSql As String

    ' 遍历所有的 table
    Dim ri As Long
    ri = scanStartRowNo
    While ri <= scanEndRowNo
        ' 1. 搜索下一个表名
        ri = findNextTableStartRowNo(sh, ri, scanEndRowNo, scanDefineTitleColNo)
        If ri = -1 Then
            GoTo loopAllTableBreak
        End If

        ' 2. 读取表数据并生成 insert sql
        tableSql = doCreateTableInsertSql(sh, ri, scanEndRowNo, scanDefineTitleColNo, endColNo)
        ri = ri + 1

        If doCreateInsertSQLForAllTableInSheetRange <> "" Then
            ' 在每个表的 sql 后面添加一个空行（最后一行除外）
            doCreateInsertSQLForAllTableInSheetRange = doCreateInsertSQLForAllTableInSheetRange & vbCrLf & vbCrLf
        End If
        doCreateInsertSQLForAllTableInSheetRange = doCreateInsertSQLForAllTableInSheetRange & tableSql
    Wend
loopAllTableBreak:

End Function

' 搜索下一个表的起始行号
' 如果已经没有 table 了，则返回 -1
Private Function findNextTableStartRowNo(ByRef sh As workSheet, ByRef startRowNo As Long, ByRef endRowNo As Long, ByRef tableDefineTitleColNo As Integer) As Long
    findNextTableStartRowNo = -1
    Dim r As Long
    With sh
        For r = startRowNo To endRowNo
            If .Cells(r, tableDefineTitleColNo) = tablePhyNameTitleContent Then
                findNextTableStartRowNo = r
                Exit Function
            End If
        Next
    End With
End Function


' 生成一个 table 的insert sql。如果发现了一个空行，则停止
' 注意:
'   startRowNo 将会被修改为 table 的最后一行的行号
'
' @param startRowNo 扫描的起始行号
' @param endRowNo 结束的行号（但是如果扫描的过程中发现了一个完全空白的行，则将会立刻停止）
' @param tableDefineTitleColNo 扫描当前 table 时，使用的 table 定义标题列的列号
' @optional-param endColNo 扫描当前 table 时，使用的最后一列的列号
'                 默认值 = -1
'                 如果是一个小于 0 的值，则将会使用扫描列物理名时发现的最后一列的列号
'                 如果是一个大于 0 的值，则在扫描列物理名时，如果发现最后一列在 endColNo 前面，那么将不会使用这个参数
'
' @return 生成的 sql
Private Function doCreateTableInsertSql(ByRef sh As workSheet, _
    ByRef startRowNo As Long, ByRef endRowNo As Long, _
    ByRef tableDefineTitleColNo As Integer, Optional ByRef endColNo As Integer = -1) As String

    ' ==================================================
    ' 1. 初始化
    ' ==================================================
    Dim tabelColPhyNameRowNo As Long
    Dim tabelColTypeRowNo As Long
    Dim tabelDataStartRowNo As Long
    Dim tableStartColNo As Integer

    tabelColPhyNameRowNo = startRowNo + columnPhyNameRowNo - 1
    tabelColTypeRowNo = startRowNo + columnTypeRowNo - 1
    tabelDataStartRowNo = startRowNo + dataStartRowNo - 1
    tableStartColNo = tableDefineTitleColNo + 1

    ' ==================================================
    ' 2. 获取表名
    ' ==================================================
    ' 在 table 的起始标题列的下一列中获取表物理名
    Dim tablePhyName As String
    tablePhyName = sh.Cells(startRowNo, tableStartColNo)

    ' ==================================================
    ' 3. 添加 truncate sql
    ' ==================================================
    If needTruncate Then
        doCreateTableInsertSql = "truncate " & tablePhyName & ";"
    End If

    ' ==================================================
    ' 4. 检查 table 的列数
    ' ==================================================
    ' 1. 扫描 table 的物理列名这一行，来获取 table 的列数
    Dim tableColCount As Integer
    tableColCount = UtilsWorksheet.getConsecutiveNoEmptyColCount(sh, tabelColPhyNameRowNo, tableStartColNo, endColNo)

    ' 2. 检查: 如果没有列，则将返回值设置为起始行，并结束
    If tableColCount = 0 Then
        ' startRowNo 不需要修改
        Exit Function
    End If

    ' 3. 计算结束列的列号
    Dim tabelColEndNo As Integer
    tabelColEndNo = tableColCount + tableDefineTitleColNo

    ' ==================================================
    ' 5. 连接每个列的物理名
    ' ==================================================
    Dim sqlColPhyNameStr As String
    If needColumnPhyName Then
        sqlColPhyNameStr = UtilsWorksheet.getRowDataStr(tabelColPhyNameRowNo, tableStartColNo, tabelColEndNo, _
        sh, ", ", emptyCellStr:="")

        sqlColPhyNameStr = "(" & sqlColPhyNameStr & ")"
    End If

    With sh
    ' ==================================================
    ' 6. 扫描列的数据类型，确认生成 sql 时，每个列的边界字符
    ' ==================================================
        Dim wrapperStrArr() As String
        ReDim wrapperStrArr(tableColCount)

        Dim ci As Integer
        For ci = 1 To tableColCount
            Select Case LCase(.Cells(tabelColTypeRowNo, tableStartColNo + ci - 1))
                ' 字符串 + 时间戳类型，边界字符为 ["]；其他类型不需要辩解字符串
                Case "char"
                    wrapperStrArr(ci) = "'"
                Case "varchar"
                    wrapperStrArr(ci) = "'"
                Case "timestamp"
                    wrapperStrArr(ci) = "'"
                Case Else
                    wrapperStrArr(ci) = ""
            End Select
        Next

    ' ==================================================
    ' 7. 遍历每一行数据，并生成 insert sql
    ' ==================================================
        Dim wrapperStr As String
        Dim sqlDataStr As String
        Dim colStr As String

        Dim ri As Long
        For ri = tabelDataStartRowNo To endRowNo
            ' 1. 检查: 如果发现了一个空行，则退出
            If UtilsWorksheet.hasNoEmptyCellInRowRange(sh, ri ,tableStartColNo, tabelColEndNo) = False Then
                Exit For
            End If

            ' 2. 检查: 如果发现了表名的标题，则说明进入了下一个 table，则停止
            If .Cells(ri, tableDefineTitleColNo) = tablePhyNameTitleContent Then
                Exit For
            End If

            ' 2. 连接数据
            sqlDataStr = ""
            For ci = 1 To tableColCount
                wrapperStr = wrapperStrArr(ci)

                colStr = .Cells(ri, tableStartColNo + ci - 1)

                If colStr = "" Then
                    sqlDataStr = sqlDataStr & "NULL"
                Else
                    sqlDataStr = sqlDataStr & wrapperStr & colStr & wrapperStr
                End If

                If ci <> tableColCount Then
                    sqlDataStr = sqlDataStr & ", "
                End If
            Next

            ' 3. 生成 insert sql
            If doCreateTableInsertSql <> "" Then
                doCreateTableInsertSql = doCreateTableInsertSql & vbCrLf
            End If
            doCreateTableInsertSql = doCreateTableInsertSql & _
                    "insert into " & tablePhyName & sqlColPhyNameStr & _
                    " values (" & sqlDataStr & ");"
        Next
    End With

    ' 将 startRowNo 修改为 table 的最后一行
    startRowNo = ri - 1
End Function


' ======================================================================
' 从一个单元格开始生成一个 table 的 insert sql
' ======================================================================
' 从一个单元格开始，向下扫描数据，并生成 insert sql
Private Function doCreateInsertSQLFromOneCell(ByRef sh As Worksheet, ByRef dataRange As Range) As String
    ' 获取 sheet 被使用范围的最后一行的行号
    Dim shEndRowNo As Long
    shEndRowNo = UtilsWorksheet.getUsedRangeEndRowNo(sh)

    Dim startRowNo As Long
    startRowNo = dataRange.Row
    ' 生成当前这个单元格标记的 table 的 insert sql
    ' dataRange.Column - 1 = 这里将单元格的前一列设置为 table 的定义标题列
    doCreateInsertSQLFromOneCell = doCreateInsertSQLForAllTableInSheetRange(sh, _
        startRowNo:=startRowNo, endRowNo:=shEndRowNo, _
        rangeDefineTitleColNo:=dataRange.Column - 1)
End Function

' 读取一个区域内的数据并生成 insert sql
Private Function doCreateInsertSQLFromRange(ByRef sh As Worksheet, ByRef dataRange As Range) As String
    doCreateInsertSQLFromRange = doCreateInsertSQLForAllTableInSheetRange(sh, _
        startRowNo:=dataRange.Row, endRowNo:=UtilsWorksheet.getRangeLastRowNo(dataRange), _
        rangeDefineTitleColNo:=dataRange.Column - 1, endColNo:= UtilsWorksheet.getRangeLastColNo(dataRange))
End Function
